﻿using BCCommon;
using BCEntity.TMS.Agent.AgentRecord;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using BCCommon.Enums;

namespace BCData.TMS.Agent.AgentRecord
{
    public class AgentRecordData : IAgentRecordData
    {
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT AgentRecordId,CompanyId,AgentLevel,ParentCompanyId,CompanyPathCode,CreateAdminId,CreateAdminName,CreateTime,
                                    UpdateTime,EditAdminId,EditAdminName,AgentRecordUuid,EnabledManualAssign,ManualAssignWaitTime FROM AgentRecord";

        private const string QUERY_JOIN = @"SELECT A.AgentRecordId,A.CompanyId,A.AgentLevel,A.ParentCompanyId,A.CompanyPathCode,A.CreateAdminId,A.CreateAdminName,A.CreateTime,
                                    A.UpdateTime,A.EditAdminId,A.EditAdminName,A.AgentRecordUuid,A.EnabledManualAssign,A.ManualAssignWaitTime,B.CompanyName,C.CompanyName AS ParentCompanyName 
                                    FROM AgentRecord A 
                                    INNER JOIN Company B ON A.CompanyId = B.CompanyId
                                    INNER JOIN Company C ON A.ParentCompanyId = C.CompanyId";

        public AgentRecordData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<AgentRecordEntity> GetEntityByCompanyId(int companyId)
        {
            return await database.Slave.QueryEntityAsync<AgentRecordEntity>($"{QUERY} WHERE CompanyId = ?CompanyId", new { companyId });
        }

        public async Task<AgentRecordEntity> Insert(AgentRecordEntity entity)
        {
            return await database.Master.InsertAsync(entity);
        }

        public async Task<AgentRecordEntity> GetEntity(long agentRecordId)
        {
            return await database.Slave.QueryEntityAsync<AgentRecordEntity>($"{QUERY} WHERE AgentRecordId = ?AgentRecordId", new { agentRecordId });
        }

        public async Task<AgentRecordEntity> Update(AgentRecordEntity entity)
        {
            return await database.Master.UpdateAsync(entity);
        }

        public async Task<(IEnumerable<AgentRecordViewEntity>, int)> Query(int? logonCmpanyId, int? companyId, int? parentCompanyId, int? agentLevel, bool? isDelete,
            DateTime? createStartDate, DateTime? createEndDate, CompanyBusinessType? businessType, string companyName, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (logonCmpanyId.HasValue)
            {
                wheres.Add(@"A.CompanyId = ?LogonCmpanyId OR (FIND_IN_SET(?LogonCmpanyId,A.CompanyPathCode) 
                            AND A.AgentLevel = (SELECT A.AgentLevel FROM agentrecord A
                            WHERE A.CompanyId = ?LogonCmpanyId) + 1)");
                parameters.Add("?LogonCmpanyId", logonCmpanyId.Value, DbType.Int32);
            }
            if (companyId.HasValue)
            {
                wheres.Add("A.CompanyId = ?CompanyId");
                parameters.Add("?CompanyId", companyId.Value, DbType.Int32);
            }
            if (parentCompanyId.HasValue)
            {
                wheres.Add("A.ParentCompanyId = ?ParentCompanyId");
                parameters.Add("?ParentCompanyId", parentCompanyId.Value, DbType.Int32);
            }
            if (agentLevel.HasValue)
            {
                wheres.Add("A.AgentLevel= ?AgentLevel");
                parameters.Add("?AgentLevel", agentLevel.Value, DbType.Int32);
            }
            if (isDelete.HasValue)
            {
                wheres.Add("A.IsDelete = ?IsDelete");
                parameters.Add("?IsDelete", isDelete.Value, DbType.Int32);
            }
            if (createStartDate.HasValue)
            {
                wheres.Add("A.CreateTime >= ?CreateStartDate");
                parameters.Add("?CreateStartDate", createStartDate.Value.Date, DbType.Date);
            }
            if (createEndDate.HasValue)
            {
                wheres.Add("A.CreateTime < ?CreateEndDate");
                parameters.Add("?CreateEndDate", createEndDate.Value.AddDays(1).Date, DbType.Date);
            }
            if (businessType.HasValue)
            {
                wheres.Add("B.BusinessType = ?BusinessType");
                parameters.Add("?BusinessType", businessType.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(companyName))
            {
                wheres.Add("B.CompanyName LIKE ?CompanyName");
                parameters.Add("?CompanyName", companyName, DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ORDER BY A.AgentRecordId DESC LIMIT {2},{3};
                                    SELECT COUNT(1) FROM AgentRecord A 
                                    INNER JOIN Company B ON A.CompanyId = B.CompanyId
                                    INNER JOIN Company C ON A.ParentCompanyId = C.CompanyId {1}",
                                    QUERY_JOIN,
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                                    (pageIndex - 1) * pageSize,
                                    pageSize);
            return await database.Slave.QueryPageableListAsync<AgentRecordViewEntity>(sql, parameters);
        }

        public async Task<AgentRecordEntity> GetByUniqueCodeAsync(string uniqueCode)
        {
            return await database.Slave.QueryEntityAsync<AgentRecordEntity>($"{QUERY} WHERE AgentRecordUuid = ?AgentRecordUuid", new { AgentRecordUuid = uniqueCode });
        }

        public async Task<AgentRecordViewEntity> GetViewByAgentRecordIdAsync(long agentRecordId)
        {
            return await database.Slave.QueryEntityAsync<AgentRecordViewEntity>($"{QUERY_JOIN} WHERE A.AgentRecordId = ?AgentRecordId", new { AgentRecordId = agentRecordId });
        }
        public async Task<AgentRecordEntity> GetTopAgentByCountyCode(string countyCode)
        {
            string sql = @"SELECT A.AgentRecordId,A.CompanyId,A.AgentLevel,A.ParentCompanyId,A.CompanyPathCode,A.CreateAdminId,A.CreateAdminName,A.CreateTime,
                                    A.UpdateTime,A.EditAdminId,A.EditAdminName,A.AgentRecordUuid,A.EnabledManualAssign,A.ManualAssignWaitTime FROM AgentRecord A 
                           INNER JOIN AgentAreaRecord B ON B.AgentRecordId=A.AgentRecordId WHERE B.CountyCode=?CountyCode ORDER BY A.AgentLevel;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            return await database.Slave.QueryFirstOrDefaultAsync<AgentRecordEntity>(sql, parameters);
        }
    }
}
